﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using Common.Log;
using WF_Business;

namespace Business.Interface
{
    /// <summary>
    /// 统一用户平台接口
    /// </summary>
    public class CommonUserInface
    {
        /// <summary>
        /// 分析xml数据并形成数据集
        /// </summary>
        /// <param name="strXml">xml文件内容</param>
        /// <returns></returns>
        public DataSet GetDatasetFromXml(string strXml)
        {
            DataSet dsTemp = new DataSet();
            System.IO.StringReader reader = new System.IO.StringReader(strXml);
            dsTemp.ReadXml(reader);
            return dsTemp;
        }

        /// <summary>
        /// 读取文件返回dataset
        /// </summary>
        /// <param name="strPath"></param>
        /// <returns></returns>
        public DataSet GetDatasetFromFile(string strPath)
        {
            string strContent = File.ReadAllText(strPath);
            return GetDatasetFromXml(strContent);
        }

        /// <summary>
        /// 根据各列名和字段值生成sql并执行
        /// </summary>
        /// <param name="drParam">数据列</param>
        /// <param name="strTableName">表名</param>
        /// <param name="strUpdateFlag">更新标志</param>
        /// <returns></returns>
        public void GenerateSqlByFields(DataRow drParam, string strTableName, string strUpdateFlag)
        {
            string strWhere = string.Empty;
            string strRelationSql = string.Empty;//关联表对应sql

            List<string> lstFiledNames = new List<string>();
            List<string> lstFieldValues = new List<string>();
            foreach (DataColumn dcOne in drParam.Table.Columns)
            {
                if (strTableName == "ST_DEPARTMENT")
                {
                    switch (dcOne.ColumnName)
                    {
                        case "organId"://主键id
                            lstFiledNames.Add("DEPARTID");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            strWhere = string.Format(" and DEPARTID='{0}'", drParam[dcOne.ColumnName]);
                            break;
                        case "organName"://部门信息
                            lstFiledNames.Add("DEPART_NAME");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "parentId":
                            lstFiledNames.Add("PARENT_ID");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "struOrder"://排序
                            lstFiledNames.Add("ORDER_ID");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                    }//switch判断
                }
                else if (strTableName == "ST_USER")
                {
                    switch (dcOne.ColumnName)
                    {
                        case "struId"://人员结构内码id
                            lstFiledNames.Add("USERID");
                            strWhere = string.Format(" and USERID='{0}'", drParam[dcOne.ColumnName]);
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "userId"://用户信息
                            lstFiledNames.Add("LOGIN_NAME");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "userName":
                            lstFiledNames.Add("USER_NAME");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "password":
                            lstFiledNames.Add("PASSWORD");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "roleId"://对应角色id,处理用户和角色关联
                            string strRoleIds = drParam[dcOne.ColumnName].ToString();
                            string strRelationTabName = "st_user_group";
                            if (!string.IsNullOrEmpty(strRoleIds))
                            {
                                string[] arrRoles = strRoleIds.Split(',');

                                foreach (string strRole in arrRoles)
                                {
                                    string tempsql = string.Format("select count(*) from {0} where userid='{1}' and gid='{2}'", strRelationTabName, drParam["struId"], strRole);
                                    string strCount = SysParams.OAConnection().GetValue(tempsql);

                                    if (strCount == "0")
                                    {
                                        strRelationSql = string.Format("insert into {0}(userid,gid) values('{1}','{2}')", strRelationTabName, drParam["struId"], strRole);
                                        SysParams.OAConnection().RunSql(strRelationSql);
                                        strRelationSql = "";
                                    }
                                }
                                //if (strUpdateFlag == "insert")
                                //{
                                //    foreach (string strRole in arrRoles)
                                //    {
                                //        strRelationSql = string.Format("insert into {0}(userid,gid) values('{1}','{2}')", strRelationTabName, drParam["struId"], strRole);
                                //    }
                                //}
                                //else if (strUpdateFlag == "update")
                                //{
                                //    foreach (string strRole in arrRoles)
                                //    {
                                //        strRelationSql = string.Format("update {0} set gid='{1}' where userid='{2}'", strRelationTabName, strRole, drParam["struId"]);
                                //    }
                                //}
                            }
                            break;
                    }//switch判断
                }
                else if (strTableName == "ST_USER_DETAIL")//ST_USER_DETAIL
                {
                    switch (dcOne.ColumnName)
                    {
                        case "parentId":
                            string strRelationTabName = "ST_USER_DEPARTMENT";
                            if (strUpdateFlag == "insert")
                            {
                                strRelationSql = string.Format("insert into {0}(userid,order_id) values('{1}','{2}')", strRelationTabName, drParam["struId"], drParam[dcOne.ColumnName]);
                            }
                            else if (strUpdateFlag == "update")
                            {
                                strRelationSql = string.Format("update {0} set order_id='{1}' where userid='{2}'", strRelationTabName, drParam[dcOne.ColumnName], drParam["struId"]);
                            }

                            break;
                        case "struOrder"://排序
                            lstFiledNames.Add("ORDERBYID");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;

                        case "struId"://人员结构内码id
                            lstFiledNames.Add("USERID");
                            strWhere = string.Format(" and USERID='{0}'", drParam[dcOne.ColumnName]);
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organAddress"://人员明细
                            lstFiledNames.Add("USER_ADDRESS");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organPhone":
                            lstFiledNames.Add("USER_PHONE");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organFax":
                            lstFiledNames.Add("USER_FAX");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organZipCode":
                            lstFiledNames.Add("USER_ZIP_CODE");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organDescription":
                            lstFiledNames.Add("USER_DESCRIPTION");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organHomeAddress":
                            lstFiledNames.Add("USER_HOME_ADDRESS");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organHomePhone":
                            lstFiledNames.Add("USER_HOME_PHONE");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organHomeZipCode":
                            lstFiledNames.Add("USER_HOME_ZIP_CODE");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organMobile":
                            lstFiledNames.Add("USER_MOBILE");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organEmail":
                            lstFiledNames.Add("USER_EMAIL");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organQQ":
                            lstFiledNames.Add("USER_QQ");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organMSN":
                            lstFiledNames.Add("USER_MSN");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organJobTitles":
                            lstFiledNames.Add("USER_JOB_TITLES");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organJobLevel":
                            lstFiledNames.Add("USER_JOB_LEVEL");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organTechnology":
                            lstFiledNames.Add("USER_TECHNOLOGY");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organTechnologyLevel":
                            lstFiledNames.Add("USER_TECHNOLOGY_LEVEL");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organSex":
                            lstFiledNames.Add("USER_SEX");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organBirthday":
                            lstFiledNames.Add("USER_BIRTHDAY");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organBirth":
                            lstFiledNames.Add("USER_BIRTH");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organOrigin":
                            lstFiledNames.Add("USER_ORIGIN");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organNation":
                            lstFiledNames.Add("USER_NATION");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organCountry":
                            lstFiledNames.Add("USER_COUNTRY");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organPoliticalStatus":
                            lstFiledNames.Add("USER_POLITICAL_STATUS");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organMarriage":
                            lstFiledNames.Add("USER_MARRIAGE");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organEducation":
                            lstFiledNames.Add("USER_EDUCATION");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organDegree":
                            lstFiledNames.Add("USER_DEGREE");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organSchool":
                            lstFiledNames.Add("USER_SCHOOL");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organWorkingStatus":
                            lstFiledNames.Add("USER_WORKING_STATUS");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organAttendanceStatus":
                            lstFiledNames.Add("USER_ATTENDANCE_STATUS");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organPhoto":
                            //lstFiledNames.Add("USER_PHOTO");
                            //lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organPartyTime":
                            lstFiledNames.Add("USER_PARTY_TIME");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organWorkTime":
                            lstFiledNames.Add("USER_WORK_TIME");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "organSpecialty ":
                            lstFiledNames.Add("USER_SPECIALTY");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;

                    }//switch判断
                }//if判断
                else if (strTableName == "SYS_PARAMS")//数据字典更新
                {
                    switch (dcOne.ColumnName)
                    {
                        case "dictCode":
                            lstFiledNames.Add("PARAMCODE");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());

                            strWhere += string.Format(" and PARAMCODE='{0}'", drParam[dcOne.ColumnName]);

                            break;
                        case "dictName":
                            lstFiledNames.Add("name");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;
                        case "itemCode":
                            lstFiledNames.Add("ITEMCODE");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());

                            if (strUpdateFlag == "update")//添加数据字典子项
                            {
                                strWhere += string.Format(" and ITEMCODE='{0}'", drParam[dcOne.ColumnName]);
                            }

                            break;
                        case "itemName":
                            lstFiledNames.Add("KEYVALUE");
                            lstFieldValues.Add(drParam[dcOne.ColumnName].ToString());
                            break;

                    }
                }

            }

            string strSql = string.Empty;

            string strFieldName = string.Empty;
            string strFieldValue = string.Empty;

            try
            {
                if (strUpdateFlag == "insert")
                {
                    //先执行删除
                    if (!string.IsNullOrEmpty(strWhere))
                    {
                        if (strTableName == "ST_DEPARTMENT")
                        {
                            strSql = string.Format("delete from {0} where 1=1 {1}", strTableName, strWhere);
                            SysParams.OAConnection().RunSql(strSql);

                            strSql = string.Format("delete from st_user_department where 1=1 {0}", strWhere.Replace("DEPARTID", "order_id"));
                            SysParams.OAConnection().RunSql(strSql);
                        }
                        else if (strTableName == "ST_USER_DETAIL")
                        {
                            strSql = string.Format("delete from {0} where 1=1 {1}", strTableName, strWhere);
                            SysParams.OAConnection().RunSql(strSql);

                            strSql = string.Format("delete from st_user_department where 1=1 {0}", strWhere);
                            SysParams.OAConnection().RunSql(strSql);
                        }
                        else if (strTableName == "ST_USER")
                        {
                            strSql = string.Format("delete from {0} where 1=1 {1}", strTableName, strWhere);
                            SysParams.OAConnection().RunSql(strSql);
                        }
                        else if (strTableName == "SYS_PARAMS")
                        {
                            strRelationSql = string.Format("update {0} set KEYCODE=rownum,name=(select name from {0} where name is not null and rownum<2  {1}) where 1=1 {1}", strTableName, strWhere);
                        }
                    }

                    for (int p = 0; p < lstFiledNames.Count; p++)
                    {
                        strFieldName += string.Format("{0},", lstFiledNames[p]);
                        strFieldValue += string.Format("'{0}',", lstFieldValues[p]);
                    }
                    strFieldName = strFieldName.Substring(0, strFieldName.Length - 1);
                    strFieldValue = strFieldValue.Substring(0, strFieldValue.Length - 1);

                    strSql = string.Format("insert into {0}({1}) values({2})", strTableName, strFieldName, strFieldValue);
                }
                else if (strUpdateFlag == "update" && !string.IsNullOrEmpty(strWhere))
                {
                    for (int p = 0; p < lstFiledNames.Count; p++)
                    {
                        strFieldName += string.Format("{0}='{1}',", lstFiledNames[p], lstFieldValues[p]);
                    }
                    strFieldName = strFieldName.Substring(0, strFieldName.Length - 1);
                    strSql = string.Format("update {0} set {1} where 1=1 {2}", strTableName, strFieldName, strWhere);
                }
                else if (strUpdateFlag == "delete" && !string.IsNullOrEmpty(strWhere))
                {
                    strSql = string.Format("delete from {0} where 1=1 {1}", strTableName, strWhere);
                }

                //执行本表操作
                if (!string.IsNullOrEmpty(strSql))
                {
                    SysParams.OAConnection().RunSql(strSql);
                }

                //执行关联关系sql
                if (!string.IsNullOrEmpty(strRelationSql))
                {
                    SysParams.OAConnection().RunSql(strRelationSql);
                }
            }
            catch (Exception ex)
            {
                LogManage.addLogErr("AnalyseXml_update", strSql + " " + ex.Message);
            }
        }

        /// <summary>
        /// 获取最大版本号
        /// </summary>
        /// <param name="strInterfaceName">接口名</param>
        /// <returns></returns>
        public string GetMaxVersionId(string strInterfaceName)
        {
            //存储最大版本号
            string strSql = string.Format("select MAX_VERSION from XT_WEBSERVICE where WEBNAME='{0}'", strInterfaceName);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 更新最大版本号
        /// </summary>
        /// <param name="strMaxVersionId">最大版本号</param>
        /// <param name="strInterfaceName">接口名</param>
        public void UpdateMaxVersionId(string strMaxVersionId, string strInterfaceName)
        {
            //存储最大版本号
            string strSql = string.Format("update XT_WEBSERVICE set MAX_VERSION='{0}' where WEBNAME='{1}'", strMaxVersionId, strInterfaceName);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 分析xml并执行相应的操作
        /// </summary>
        /// <param name="strXml"></param>
        /// <param name="strInterfaceName">接口名</param>
        public void AnalyseXml(string strXml, string strInterfaceName)
        {
            try
            {
                DataSet dsReturn = GetDatasetFromXml(strXml);
                if (dsReturn.Tables.Count > 1)//保证内容不为空
                {
                    DataTable dtType = dsReturn.Tables["trans"];
                    DataTable dtContent = dsReturn.Tables["transXml"];

                    if (dtType.Rows.Count > 0)//保证有值
                    {
                        string strFilter = "transType<>'ADD_POST' and transType<>'DELETE_POST'  and transType<>'UPDATE_POST' and transType<>'MERGE_POST' ";
                        DataRow[] drsType = dtType.Select(strFilter, "trans_Id");

                        foreach (DataRow drType in drsType)
                        {
                            strFilter = string.Format("trans_Id='{0}'", drType["trans_Id"]);
                            string strUpdateFlag = drType["transType"].ToString();
                            DataRow[] drsContents = dtContent.Select(strFilter);

                            string strSql = string.Empty;
                            switch (strUpdateFlag.ToUpper())
                            {
                                case "ADD_CORP"://新增单位
                                case "ADD_DEP"://新增部门
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "ST_DEPARTMENT", "insert");
                                    }
                                    break;
                                case "DELETE_CORP"://删除单位
                                case "DELETE_DEP"://删除部门
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "ST_DEPARTMENT", "delete");
                                    }
                                    break;
                                case "UPDATE_CORP"://更新单位
                                case "UPDATE_DEP"://更新部门信息
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "ST_DEPARTMENT", "update");
                                    }
                                    break;
                                case "MERGE_CORP"://变更单位隶属
                                case "MERGE_DEP"://变更部门隶属关系   
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "ST_DEPARTMENT", "update");
                                    }
                                    break;
                                case "ADD_EMP"://新增人员
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "ST_USER_DETAIL", "insert");
                                    }
                                    break;
                                case "DELETE_EMP"://删除人员
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "ST_USER_DETAIL", "delete");
                                    }
                                    break;
                                case "UPDATE_EMP"://更新人员信息
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "ST_USER_DETAIL", "update");
                                    }
                                    break;
                                case "MERGE_EMP"://变更人员隶属关系
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "ST_USER_DETAIL", "update");
                                    }
                                    break;
                                case "MULTIPLE_EMP"://人员兼职
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "ST_USER", "update");
                                    }
                                    break;
                                case "ADD_USER"://新增用户
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "ST_USER", "insert");
                                    }
                                    break;
                                case "DELETE_USER"://删除用户
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "ST_USER", "delete");
                                    }
                                    break;
                                case "UPDATE_USER"://更新用户信息
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "ST_USER", "update");
                                    }
                                    break;

                                case "ADD_DICTIONARY"://新增数据字典
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "SYS_PARAMS", "insert");
                                    }
                                    break;
                                case "ADD_DICTIONARYITEM"://新增数据字典子项
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "SYS_PARAMS", "insert");
                                    }
                                    break;
                                case "UPDATE_DICTIONARY"://修改数据字典
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "SYS_PARAMS", "update");
                                    }
                                    break;
                                case "UPDATE_DICTIONARYITEM"://修改数据字典子项
                                    foreach (DataRow drContent in drsContents)
                                    {
                                        GenerateSqlByFields(drContent, "SYS_PARAMS", "update");
                                    }
                                    break;
                            }
                        }

                        //更新最大版本号
                        if (dtType.Rows.Count > 0)
                        {
                            UpdateMaxVersionId(dtType.Rows[dtType.Rows.Count - 1]["versionId"].ToString(), strInterfaceName);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogManage.addLogErr("AnalyseXml", ex.Message);
            }
        }


        /// <summary>
        /// 初始化xml数据
        /// </summary>
        public void InitXml()
        {
            string strPath = "d:\\initData.xml";
            DataSet dsReturn = GetDatasetFromFile(strPath);
            if (dsReturn.Tables.Count > 1)//保证内容不为空
            {
                try
                {
                    DataTable dtCorporation = dsReturn.Tables["corporation"];
                    DataTable dtDepartment = dsReturn.Tables["department"];
                    DataTable dtEmployee = dsReturn.Tables["employee"];
                    DataTable dtUser = dsReturn.Tables["user"];

                    InsertData(dtCorporation, "ST_DEPARTMENT");
                    InsertData(dtDepartment, "ST_DEPARTMENT");
                    InsertData(dtEmployee, "ST_USER_DETAIL");
                    InsertData(dtUser, "ST_USER");
                }
                catch (Exception ex)
                {
                    LogManage.addLogErr("InitXml", ex.Message);
                }

            }
        }

        /// <summary>
        /// 插入表格中数据
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="strTableName"></param>
        private void InsertData(DataTable dtSource, string strTableName)
        {
            string strSql = string.Empty;
            foreach (DataRow drContent in dtSource.Rows)
            {
                GenerateSqlByFields(drContent, strTableName, "insert");
            }
        }
    }
}
